﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
namespace PassportPositionExport
{
    public partial class Form1 : Form
    {
        string sqlConn=@"Data Source=(local)\SQL2005;Initial Catalog=PassportManage;Integrated Security=True";
        public Form1()
        {
            InitializeComponent();
        }
        DateTime exDT;
        private void Form1_Load(object sender, EventArgs e)
        {
            PPEx();
            exDT = DateTime.Now;
            timer1.Enabled = true;
        }
        void PPEx()
        {
            DataTable dt = DataTableSQL("select * from PassportInfo");
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false;
                Microsoft.Office.Interop.Excel.Workbook wBook = app.Workbooks.Add(true);
                Microsoft.Office.Interop.Excel.Worksheet wSheet = (Microsoft.Office.Interop.Excel.Worksheet)wBook.Worksheets[wBook.Worksheets.Count];
                Microsoft.Office.Interop.Excel.Range range;
                wSheet.Name = "护照位置"+DateTime.Now.ToString("yy-MM-dd");
                wSheet.Cells[1, 1] = "条码";
                wSheet.Cells[1, 2] = "证件号";
                wSheet.Cells[1, 3] = "生日";
                wSheet.Cells[1, 4] = "身份证";
                wSheet.Cells[1, 5] = "姓名";
                wSheet.Cells[1, 6] = "业务编号";
                wSheet.Cells[1, 7] = "层";
                wSheet.Cells[1, 8] = "单元";
                wSheet.Cells[1, 9] = "格";
                range = wSheet.get_Range("A1", "I1");
                range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkOrange);
                range.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    wSheet.Cells[2 + i, 1] = dr["TaskCode"].ToString();
                    wSheet.Cells[2 + i, 2] = dr["CardCode"].ToString();
                    wSheet.Cells[2 + i, 3] = dr["Birthday"].ToString();
                    wSheet.Cells[2 + i, 4] = dr["CardID"].ToString();
                    wSheet.Cells[2 + i, 5] = dr["Name"].ToString();
                    wSheet.Cells[2 + i, 6] = dr["OprationNumber"].ToString();
                    wSheet.Cells[2 + i, 7] = (Convert.ToInt16(dr["Layer"]) - 1).ToString();
                    wSheet.Cells[2 + i, 8] = ((Convert.ToInt16(dr["Cell"]) - 1) / 10).ToString();
                    wSheet.Cells[2 + i, 9] = ((Convert.ToInt16(dr["Cell"]) - 1) % 10).ToString();
                    if (i % 2 == 1)
                    {
                        range = wSheet.get_Range("A" + (2 + i).ToString(), "I" + (2 + i).ToString());
                        range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
                        range.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
                    }
                }
                range = wSheet.get_Range("A1", "I" + (dt.Rows.Count + 1).ToString());
                range.Columns.AutoFit();
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                wBook.Saved = true;
                if (!Directory.Exists(System.Windows.Forms.Application.StartupPath+"\\护照位置数据"))
                    Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\护照位置数据");
                wBook.SaveCopyAs(System.Windows.Forms.Application.StartupPath+"\\护照位置数据"+"\\护照位置"+DateTime.Now.ToString("YY-MM-dd")+".xls");
                wBook.Close(true, Type.Missing, Type.Missing);
                app.Quit();
                textBox1.AppendText(DateTime.Now.ToShortDateString() + "导出护照位置成功"+"\r\n");
            }
            catch (System.Exception ex)
            {
                textBox1.AppendText(DateTime.Now.ToShortDateString() + "导出护照位置失败"+"\r\n");
                textBox1.AppendText(DateTime.Now.ToShortDateString() + "失败原因"+ex.Message+"\r\n");
                app.DisplayAlerts = false;
                app.Quit();
                return;
            }
           
        }
        public DataTable DataTableSQL(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(sqlConn))
            {
                DataTable dt = new DataTable();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(dt);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return dt;
            }
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            if (exDT.AddDays(1).Date == DateTime.Now.Date)
            {
                if (DateTime.Now.Hour == 17)
                {
                    PPEx();
                    exDT = DateTime.Now;
                }
            }
        }
    }
}
